--    Author    : ChenErHao
--    Name      : DM.T_MTH_LOAN_Y_AMOUNT_GROWTH_TOTAL.HQL
--    Functions : 增速-贷款当年发生额增速分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_LOAN_Y_AMOUNT_GROWTH_TOTAL PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 

SELECT
    T1.ORG_ID,
    nvl(T1.CUSTOMER_TYPE,9999),
    T1.PRODUCT_CATEGORY,
    nvl(T1.LOAN_PERIOD,9999), 
    T1.LPIC, 
    T1.LOANS_ACTUALLY, 
    T1.ENTERPRISE_SCALE, 
    T1.ENTERPRISE_INVESTOR, 
    T1.GUARANTY_STYLE, 
    T1.LOAN_STATUS, 
    '' LOAN_LOAN_QUALITY, 
    nvl(T1.AREA_NO_ID,9999),
     nvl(SUM(T1.LOAN_AMOUNT),0)   LOAN_AMOUNT,
     nvl(SUM(T1.LAST_YEAR_LOAN_AMOUNT),0)    LAST_YEAR_LOAN_AMOUNT,
     nvl(SUM(T1.WEIGHT_AMT),0)   WEIGHT_AMT
FROM
    (
        SELECT
            BELONG_AREA_CODE  AREA_NO_ID,            
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            A.CUSTOMER_TYPE         CUSTOMER_TYPE,
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY, 
            A.LOANS_PERIOD           LOAN_PERIOD,
            NVL(LPIC_SUB,'9999') LPIC, 
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY, 
            nvl(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE, 
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,  
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,            
            nvl(LOAN_STATUS,'9999')            LOAN_STATUS, 
            SUM(LOAN_OCCURRENCE)   LOAN_AMOUNT,0 LAST_YEAR_LOAN_AMOUNT,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
           (SELECT BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY, LPIC_SUB,LOANS_ACTUALLY,
           ENTERPRISE_INVESTOR,GUARANTY_STYLE,CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,
           LOAN_OCCURRENCE,LOAN_OCCURRENCE_LEVEL FROM 
            EDW.LOAF 
        WHERE
            DATA_DATE BETWEEN '#V_YEAR_FIRST_DAY#' AND '#V_DATA_DATE#' 
        )A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end= B.ORG_ID  
        LEFT JOIN DMCODE.t_loan_pro_type C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.LOAN_PRO_ID 
        LEFT JOIN DMCODE.t_loans_actually G ON CASE WHEN a.LOANS_ACTUALLY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.LOANS_ACTUALLY end=G.loan_actually_id   
        LEFT JOIN DMCODE.t_investor_finicl_type_trans E ON CASE WHEN a.ENTERPRISE_INVESTOR IS NULL THEN CONCAT('org_code_hive',RAND()) else A.ENTERPRISE_INVESTOR end= E.investor_finicl_id 
        LEFT JOIN DMCODE.t_loan_insur_type F ON CASE WHEN a.GUARANTY_STYLE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.GUARANTY_STYLE end=F.loan_insur_id  
        GROUP BY
            A.BELONG_AREA_CODE  ,   B.ORG_LVL_ID_2  ,            
            A.CUSTOMER_TYPE,  C.LOAN_PRO_ID_1  ,
            LOANS_PERIOD,   LPIC_SUB  , G.loan_actually_id_1  ,
            A.ENTERPRISE_SCALE,   E.INVESTOR_FINICL_ID_2  , F.LOAN_INSUR_ID_1   ,
            LOAN_STATUS
            
    union all
    
        SELECT
            AREA_NO_ID,
            ORG_ID,
            CUSTOMER_TYPE,
            PRODUCT_CATEGORY,
            LOAN_PERIOD, 
            LPIC, 
            LOANS_ACTUALLY, 
            ENTERPRISE_SCALE, 
            ENTERPRISE_INVESTOR, 
            GUARANTY_STYLE, 
            LOAN_STATUS, 
            0 LOAN_AMOUNT,
            SUM(LOAN_AMOUNT) LAST_YEAR_LOAN_AMOUNT,
            0  WEIGHT_AMT
        FROM
            DM.T_MTH_LOAN_Y_AMOUNT_GROWTH_TOTAL
        WHERE
            FISCAL_MTH_DIMNSN_ID ='#V_LAST_12M_END_MONTH#'
        GROUP BY
            AREA_NO_ID,
            ORG_ID,
            CUSTOMER_TYPE,
            PRODUCT_CATEGORY,
            LOAN_PERIOD,
            LPIC,
            LOANS_ACTUALLY,
            ENTERPRISE_SCALE,
            ENTERPRISE_INVESTOR,
            GUARANTY_STYLE,
            LOAN_STATUS) T1 
GROUP BY
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD, 
    T1.LPIC, 
    T1.LOANS_ACTUALLY, 
    T1.ENTERPRISE_SCALE, 
    T1.ENTERPRISE_INVESTOR, 
    T1.GUARANTY_STYLE, 
    T1.LOAN_STATUS;